package com.lsy.shop.dao;

import cn.itcast.jdbc.TxQueryRunner;
import com.lsy.shop.domain.Customer;
import com.lsy.shop.domain.PageBean;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 持久层
 *
 * @author lenovo
 */
public class CustomerDao {

    private TxQueryRunner queryRunner = new TxQueryRunner();

    /**
     * 添加客户
     *
     * @param customer
     */
    public void add(Customer customer) {
        try {
            String sql = "insert into t_customer values(?,?,?,?,?,?,?,?,?)";
            Object[] parms = {customer.getCid(), customer.getCname(), customer.getGender(), customer.getBirthday(), customer.getCellphone(), customer.getEmail(), customer.getDescription(),customer.getUsername(),customer.getPassword()};
            queryRunner.update(sql, parms);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 查找所有的客户
     *
     * @return
     */
    public PageBean<Customer> findAll(int pc, int ps) {
        try {
            PageBean<Customer> pageBean = new PageBean<Customer>();
            pageBean.setPs(ps);
            pageBean.setPc(pc);
            String sql = "select count(*) from t_customer";
            Number tr = (Number) queryRunner.query(sql, new ScalarHandler());
            pageBean.setTr(tr.intValue());
            sql = "select * from t_customer order by cname limit ?,?";
            List<Customer> beanlist = queryRunner.query(sql, new BeanListHandler<Customer>(Customer.class), (pc - 1) * ps, ps);
            pageBean.setBeanList(beanlist);

            return pageBean;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }


    /**
     * 根据cid返回客户信息
     *
     * @param cid
     * @return
     */
    public Customer findByid(String cid) {
        try {
            String sql = "select * from t_customer where cid =?";
            Object[] parms = {cid};
            return queryRunner.query(sql, new BeanHandler<Customer>(Customer.class), parms);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 对客户信息进行更改
     *
     * @param customer
     */
    public void edit(Customer customer) {
        try {
            String sql = "update t_customer set cname = ?,gender = ?,birthday = ?,cellphone = ?,email = ? ,description = ?,username = ?, passowrd = ? where cid = ?";
            Object[] parms = {customer.getCname(), customer.getGender(), customer.getBirthday(), customer.getCellphone(), customer.getEmail(), customer.getDescription(), customer.getCid(),customer.getUsername(),customer.getPassword()};
            queryRunner.update(sql, parms);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 根据cid来删除信息
     *
     * @param cid
     */
    public void delete(String cid) {
        try {
            String sql = "delete  from t_customer where cid = ?";
            Object[] parms = {cid};
            queryRunner.update(sql, parms);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     *
     *
     * @param customer
     * @return
     */
//    public List<Customer> query(Customer customer) {
//        //创建查找模板
//        StringBuffer sqlbuffer = new StringBuffer("select *from t_customer where 1=1");
//        List<Object> parms= new ArrayList<>();
//        String cname = customer.getCname();
//        if(cname != null && !cname.trim().isEmpty())
//        {
//            sqlbuffer.append(" and cname like ?");
//            parms.add("%"+cname+"%");
//        }
//        String gender = customer.getGender();
//        if(gender!=null && !gender.trim().isEmpty())
//        {
//            sqlbuffer.append(" and gender = ?");
//            parms.add(gender);
//        }
//        String cellphone = customer.getCellphone();
//        if(cellphone != null && !cellphone.trim().isEmpty())
//        {
//            sqlbuffer.append(" and cellphone like ?");
//            parms.add("%"+cellphone+"%");
//        }
//        String email = customer.getEmail();
//        if(email != null && !email.trim().isEmpty())
//        {
//            sqlbuffer.append(" and email like ?");
//            parms.add("%"+email+"%");
//        }
//
//        String sql = sqlbuffer.toString();
//        try {
//          return queryRunner.query(sql,new BeanListHandler<Customer>(Customer.class),parms.toArray());
//        } catch (SQLException e) {
//            throw new RuntimeException(e);
//        }
//    }

    /**
     * 保留url的搜索
     *
     * @param
     * @return
     */
//    public PageBean<Customer> query(Customer customer,int pc,int ps) {
//        /**
//         * 1.创建pageBean
//         * 2.设置已有的属性,ps,pc
//         * 3.得到tr
//         * 4.得到beanlist
//         */
//        try{
//        PageBean<Customer> pageBean = new PageBean<>();
//        pageBean.setPc(pc);
//        pageBean.setPs(ps);
//
//        //sql语句前半句
//        StringBuffer consql  = new StringBuffer("select count(*) from t_customer");
//        StringBuffer wheresql = new StringBuffer(" where 1 = 1");
//
//
//        //判断条件,完成语句后面的追加
//        List<Object> parms= new ArrayList<>();
//        String cname = customer.getCname();
//        if(cname != null && !cname.trim().isEmpty())
//        {
//            wheresql.append(" and cname like ?");
//            parms.add("%"+cname+"%");
//        }
//        String gender = customer.getGender();
//        if(gender!=null && !gender.trim().isEmpty())
//        {
//            wheresql.append(" and gender = ?");
//            parms.add(gender);
//        }
//        String cellphone = customer.getCellphone();
//        if(cellphone != null && !cellphone.trim().isEmpty())
//        {
//            wheresql.append(" and cellphone like ?");
//            parms.add("%"+cellphone+"%");
//        }
//        String email = customer.getEmail();
//        if(email != null && !email.trim().isEmpty())
//        {
//            wheresql.append(" and email like ?");
//            parms.add("%"+email+"%");
//        }
//        String sql = consql.append(wheresql).toString();
//        System.out.println(sql);
//        System.out.println(parms);
//        Number number  =  (Number)queryRunner.query(sql,new ScalarHandler(),parms.toArray());
//        int tr = number.intValue();
//        System.out.println(tr);
//        pageBean.setTr(tr);
//            /**
//             * 得到beanlist
//             *
//             */
//        StringBuffer beansql = new StringBuffer("select * from t_customer");
//        StringBuffer limitsql = new StringBuffer(" limit ?,?");
//        //limit的参数
//        parms.add((pc-1)*ps);
//        //limit的参数
//        parms.add(ps);
//        System.out.println(beansql.append(wheresql).append(limitsql).toString());
//        System.out.println(parms);
//        List<Customer> beanList = queryRunner.query(beansql.append(wheresql).append(limitsql).toString(),
//                new BeanListHandler<Customer>(Customer.class),
//                parms.toArray());
//        pageBean.setBeanList(beanList);
//        return pageBean;
//        } catch (SQLException e) {
//            throw new RuntimeException(e);
//        }
//    }
    public PageBean<Customer> query(Customer criteria, int pc, int ps) {
        try {
     /*
     * 1. 创建PageBean对象
     * 2. 设置已有的属性，pc和ps
     * 3. 得到tr
     * 4. 得到beanList
     */
     /*
     * 创建pb，设置已有属性
     */
            PageBean<Customer> pb = new PageBean<Customer>();
            pb.setPc(pc);
            pb.setPs(ps);

     /*
     * 得到tr
     */

     /*
     * 1. 给出一个sql语句前半部
     */
            StringBuilder cntSql = new StringBuilder("select count(*) from t_customer");
            StringBuilder whereSql = new StringBuilder(" where 1=1");
     /*
     * 2. 判断条件，完成向sql中追加where子句
     */
     /*
     * 3. 创建一个ArrayList，用来装载参数值
     */
            List<Object> params = new ArrayList<Object>();
            String cname = criteria.getCname();
            if (cname != null && !cname.trim().isEmpty()) {
                whereSql.append(" and cname like ?");
                params.add("%" + cname + "%");
            }

            String gender = criteria.getGender();
            if (gender != null && !gender.trim().isEmpty()) {
                whereSql.append(" and gender=?");
                params.add(gender);
            }

            String cellphone = criteria.getCellphone();
            if (cellphone != null && !cellphone.trim().isEmpty()) {
                whereSql.append(" and cellphone like ?");
                params.add("%" + cellphone + "%");
            }

            String email = criteria.getEmail();
            if (email != null && !email.trim().isEmpty()) {
                whereSql.append(" and email like ?");
                params.add("%" + email + "%");
            }

     /*
     * select count(*) .. + where
     *
     */
            Number num = (Number) queryRunner.query(cntSql.append(whereSql).toString(),
                    new ScalarHandler(), params.toArray());
            int tr = num.intValue();
            pb.setTr(tr);

     /*
     * 得到beanList
     */
            StringBuilder sql = new StringBuilder("select * from t_customer");
            // 查询返回所有的信息
            StringBuilder limitSql = new StringBuilder(" limit ?,?");
            // limit的值
            params.add((pc - 1) * ps);
            params.add(ps);
            // 执行sql语句
            List<Customer> beanList = queryRunner.query(sql.append(whereSql).append(limitSql).toString(),
                    new BeanListHandler<Customer>(Customer.class),
                    params.toArray());
            pb.setBeanList(beanList);
            System.out.println(beanList.size());
            System.out.println(pb.toString());
            return pb;

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}
